<?php

use console\db\Migration;
use yii\db\Query;
use yii\helpers\Console;

/**
 * 旧系统用户表数据过滤
 * 
 * @author emhome <emhome@163.com>
 * @since 1.0
 */
class m300000_000001_t_filter_member extends Migration {

    /**
     * @inheritdoc
     */
    public $tableName = '{{%member}}';

    /**
     * @inheritdoc
     */
    public function safeUp() {
        $this->filterUserName();
        $this->update($this->tableName, ['mobile' => null], ['mobile' => '']);
        $this->update($this->tableName, ['email' => null], ['email' => '']);
        $this->filterUnqiueUserName();
        $this->filterUnqiueMobile();
        $this->filterUnqiueEmail();
    }

    /**
     * 过滤非法用户名的用户
     * @desc 用户名中包含sql以及js代码的全部删除，处理方式为直接删除
     * @inheritdoc
     */
    protected function filterUserName() {
        $this->delete($this->tableName, [
            'or',
            ['like', 'username', 'INSERT INTO'],
            ['like', 'username', 'script'],
        ]);
    }

    /**
     * 用户名重复用户清理，处理方式为直接删除
     * @inheritdoc
     */
    protected function filterUnqiueUserName() {
        $data = (new Query())->from($this->tableName)
        ->select(['username', 'count(*)'])
        ->groupBy('username')
        ->having('count(*)>1')
        ->all();
        if (empty($data)) {
            return;
        }
        foreach ($data as $user) {
            $username = $user['username'];
            Console::output("Deleteing repeat username: {$username} where lastlogintime is 0.");
            $this->delete($this->tableName, [
                'and',
                ['username' => $username],
                ['lastlogintime' => 0],
            ]);
        }
        Console::output("All deleted done. at " . __METHOD__);
    }

    /**
     * 手机号重复用户清理，处理方式为清理手机号
     * @inheritdoc
     */
    protected function filterUnqiueMobile() {
        $data = (new Query())->from($this->tableName)
        ->select(['uid', 'mobile', 'count(*)'])
        ->groupBy('mobile')
        ->having('count(*)>1')
        ->all();
        if (empty($data)) {
            return;
        }
        foreach ($data as $user) {
            $mobile = $user['mobile'];
            Console::output("Clearing repeat mobile: {$mobile} where lastlogintime is 0 or rongcloud is null.");
            $this->update($this->tableName, ['mobile' => null], [
                'and',
                ['mobile' => $mobile],
                [
                    'or',
                    ['lastlogintime' => 0],
                    ['rongcloud' => null],
                    ['!=', 'uid', $user['uid']],
                ]
            ]);
        }
        Console::output("All clear done. at " . __METHOD__);
    }

    /**
     * 邮箱重复用户清理，处理方式为清理邮箱
     * @inheritdoc
     */
    protected function filterUnqiueEmail() {
        $data = (new Query())->from($this->tableName)
        ->select(['uid', 'email', 'count(*)'])
        ->groupBy('email')
        ->having('count(*)>1')
        ->all();
        if (empty($data)) {
            return;
        }
        foreach ($data as $user) {
            $email = $user['email'];
            Console::output("Clearing repeat email: {$email} where lastlogintime is 0 or rongcloud is null.");
            $this->update($this->tableName, ['email' => null], [
                'and',
                ['email' => $email],
                [
                    'or',
                    ['lastlogintime' => 0],
                    ['rongcloud' => null],
                    ['!=', 'uid', $user['uid']],
                ]
            ]);
        }
        Console::output("All clear done. at " . __METHOD__);
    }

}
